USE [ServerMonitor]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileGrowthRate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FileGrowthRate]
GO
USE [ServerMonitor]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FileGrowthRate](@ServerId INT, @DBName NVARCHAR(256), @DataSize INT, @RecordTime DATETIME, @PerType VARCHAR(5))
RETURNS FLOAT
AS
BEGIN
  DECLARE @Ret FLOAT, @OldSize INT
  SET @OldSize = 
  CASE @PerType 
  WHEN 'DAY' THEN (SELECT TOP(1) [DataUsage]
                  FROM [ServerMonitor].[dbo].[AllServerDBUsage]
                  WHERE [ServerId] = @ServerId AND [DBName] = @DBName AND DATEDIFF(DAY,[RecordTime],@RecordTime) = 1) 
  WHEN 'WEEK' THEN (SELECT TOP(1) [DataUsage]
                  FROM [ServerMonitor].[dbo].[AllServerDBUsage]
                  WHERE [ServerId] = @ServerId AND [DBName] = @DBName AND DATEDIFF(DAY,[RecordTime],@RecordTime) = 7)
  WHEN 'MONTH' THEN (SELECT TOP(1) [DataUsage]
                  FROM [ServerMonitor].[dbo].[AllServerDBUsage]
                  WHERE [ServerId] = @ServerId AND [DBName] = @DBName AND CONVERT(VARCHAR(8),[RecordTime],112) = CONVERT(VARCHAR(8),DATEADD(MONTH,-1,@RecordTime),112))
  WHEN 'YEAR' THEN (SELECT TOP(1) [DataUsage]
                  FROM [ServerMonitor].[dbo].[AllServerDBUsage]
                  WHERE [ServerId] = @ServerId AND [DBName] = @DBName AND CONVERT(VARCHAR(8),[RecordTime],112) = CONVERT(VARCHAR(8),DATEADD(MONTH,-1,@RecordTime),112))
  ELSE 0
  END
  IF(@OldSize IS NULL OR @OldSize = 0) SET @Ret = NULL
  ELSE SET @Ret = ((@DataSize * 1.0) - @OldSize) / @OldSize
  SET @Ret = CAST(@Ret AS DECIMAL(10, 4)) 
  
  RETURN(@Ret)
END





GO


